package DBLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.SalesOrder;
import ModelLayer.SalesOrderLine;

public class DBSalesOrder implements IFDBSalesOrder {
	private Connection con;
	private PreparedStatement pStmtSelect;

	// this method tries to connect to the database
	public DBSalesOrder() {
		con = DBConnection.getInstance().getDBcon();
	}

	// this method finds a salesorder on its id
	public SalesOrder findSalesOrder(int id, boolean retriveAssociation) {
		SalesOrder salesorderObj = new SalesOrder();
		// the line calls the method findWithPreQuery where the salesorder id is the search value
		salesorderObj = findWithPreQuery(id);
		return salesorderObj;
	}

	// miscWhere is used when more than one salesorder is to be retrieved 
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		// a local arraylist i created
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();

		String query = buildQuery(wClause);
		System.out.println("DbSalesOrder " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			// start while
			while (results.next()) {
				// a new salesorder object is created
				SalesOrder salesorderObj = new SalesOrder();
				// the salesorder objects values are 
				salesorderObj = buildSalesOrder(results);
				// adds salesorders to the local arraylist
				list.add(salesorderObj);

			}// end while
			stmt.close();
		}// end try
		catch (Exception e) {
			System.out.println("Query exception - select SalesOrder: " + e);
			e.printStackTrace();
		}
		// returns the local arraylist
		return list;
	}

	// this method builds a salesOrder
	private SalesOrder buildSalesOrder(ResultSet results) {
		// a new salesOrder object is created
		SalesOrder salesorderObj = new SalesOrder();

		try {
			// the salesorders id i to be put in here
			salesorderObj.setSaleId(results.getInt("id"));
		} catch (Exception e) {
			System.out.println("building salesorder object");
		}
		// returns the salesorder object
		return salesorderObj;
	}

	// method to build the query
	private String buildQuery(String wClause) {
		String query = "Select id, from SalesOrder";

		if (wClause.length() > 0)
			query = query + "where" + wClause;

		// returns the String
		return query;
	}

	// this method finds a salesorder on its id
	public SalesOrder findWithPreQuery(int id) {
		System.out.println("Find with preCompiled");
		SalesOrder salesorderObj = null;
		ResultSet results;
		try {
			pStmtSelect.setInt(1, id);
			results = pStmtSelect.executeQuery();
			if (results.next()) {
				salesorderObj = buildSalesOrder(results);
			}
		} catch (Exception e) {
			e.getMessage();
		}
		return salesorderObj;
	}

	// this method inserts a new salesorder into the system
	public int insertSalesOrder(SalesOrder salesorderObj) {
		// here the method getMaxId from the GetMax class is called
		//it finds the highest existing salesId and counts up with one
		//this number is to be the new salesorders id
		int nextSalesId = DBGetMax.getMaxId("Select max(saleId) from SalesOrder");
		nextSalesId = nextSalesId + 1;
		salesorderObj.setSaleId(nextSalesId);
		System.out.println("next SalesId = " + nextSalesId);
		
		int rc = -1;
		PreparedStatement pstmt = null;
		String insert = "insert into SalesOrder(saleId, date, deliverystatus, deliverydate, cusPhoneNumber, orderType, empPhoneNumber)"
				+ "values (?,?,?,?,?,?,?)";
		System.out.println(insert);
		try {
			// here the rest of the attributes are to be inserted
			pstmt = con.prepareStatement(insert);

			pstmt.setInt(1, salesorderObj.getSaleId());
			pstmt.setString(2, salesorderObj.getDate());
			pstmt.setString(3, salesorderObj.getDeliveryStatus());
			pstmt.setString(4, salesorderObj.getDeliveryDate());
			pstmt.setInt(5, salesorderObj.getCustomer().getPhoneNumber());
			System.out.println(salesorderObj.getCustomer().getPhoneNumber());
			pstmt.setString(6, salesorderObj.getOrderType());
			pstmt.setInt(7, salesorderObj.getEmployee().getPhoneNumber());
			rc = pstmt.executeUpdate();
			pstmt.close();
			for(SalesOrderLine sol : salesorderObj.getSols())
			{
				insertSalesOrderLine(sol);
			}
		} catch (SQLException sqlE) {
			System.out.println("SQL Error");
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			e.getMessage();
		}
		// returns the object
		return rc;
	}
	
	public int insertSalesOrderLine(SalesOrderLine salesLineObj) {
		// here the method getMaxId from the GetMax class is called
		
		int nextSalesLineId = DBGetMax.getMaxId("Select max(salesLineId) from SalesOrderLine");
		nextSalesLineId = nextSalesLineId + 1;
		salesLineObj.setSalesLineId(nextSalesLineId);
		System.out.println("next SalesId = " + nextSalesLineId);
		
		int rc = -1;
		PreparedStatement pstmt = null;
		String insert = "insert into SalesOrderLine(salesLineId, productNumber, saleId, phoneNumber, amount)"
				+ "values (?,?,?,?,?)";
		System.out.println(insert);
		try {
			// here the rest of the attributes are to be inserted
			pstmt = con.prepareStatement(insert);
			pstmt.setInt(1, salesLineObj.getSalesLineId());
			pstmt.setInt(2, salesLineObj.getProduct().getProductNumber());
			pstmt.setInt(3, salesLineObj.getSale().getSaleId());
			pstmt.setInt(4, salesLineObj.getStore().getPhoneNumber());
			pstmt.setInt(5, salesLineObj.getAmount());
			rc = pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException sqlE) {
			System.out.println("SQL Error");
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			e.getMessage();
			System.out.println(e.getMessage());
		}
		// returns the object
		return rc;
	}

	// this method deletes a salesorder found on its id
	public int deleteSalesOrder(int salesOrderId) {
		int rc = -1;
		PreparedStatement pstmt = null;
		String delete = "delete from SalessOrder where id = ?";
		System.out.println(delete);
		try {
			pstmt = con.prepareStatement(delete);
			pstmt.setInt(1, salesOrderId);
			rc = pstmt.executeUpdate();
		} catch (SQLException sqlE) {
			System.out.println("SQL Error");
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			e.getMessage();
		}
		return rc;
	}

	// get all sales orders on a Customer
	public ArrayList<SalesOrder> findAllSalesOrdersOnCustomer(int phoneNumber) 
	{
		return miscWhere(" phoneNumber = " + phoneNumber, true);
	}
	// get all salesorder on an Employee
	public ArrayList<SalesOrder> findAllSalesOrdersOnEmployee(int phoneNumber)
	{
		return miscWhere("employeeId = " + phoneNumber, true);
	}
	// get all salesorders in the system
	public ArrayList<SalesOrder> findAllSalesOrder(boolean retriveAssociation) 
	{
		return miscWhere("", retriveAssociation);
	}
	//get all the salesorder on a invoice
	public ArrayList<SalesOrder> findAllInvoiceSalesOrders(int invoiceId) 
	{
		return miscWhere("invoiceId = " + invoiceId, true);
	}

	@Override
	public ArrayList<SalesOrder> findAllSalesOrders(boolean retriveAssociation) 
	{
		return miscWhere("", retriveAssociation);
	}

}
